﻿using System.Data;
using System;
using Code;
using System.Data.SqlClient;
using System.Data.Common;


namespace E2_WEBSITE.Code.Admin
{
    public class UserDataAccess
    {
        DataProvider provider = DataProvider.GlobalDataProvider;
        string sql = string.Empty;

        public DataTable GetSimpleUsers()
        {
            try
            {
                sql = "SELECT u.[id] ID, u.[TenDangNhap] TenDangNhap " +
                                "FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] u, [COMPUTERSHOPDB].[dbo].[LOAI_NGUOI_DUNG] t " +
                                "WHERE u.[idLoaiNguoiDung] = t.[id]";
                return provider.ExecuteQueryDataTable(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetUsers()
        {
            try
            {
                sql = "SELECT u.[id] ID, u.[TenDangNhap] TenDangNhap, t.[TenLoaiNguoiDung] LoaiNguoiDung " +
                                "FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] u, [COMPUTERSHOPDB].[dbo].[LOAI_NGUOI_DUNG] t " +
                                "WHERE u.[idLoaiNguoiDung] = t.[id]";
                return provider.ExecuteQueryDataTable(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public UserInfo GetUserById(int uid)
        {
            try
            {
                sql = string.Format("SELECT [id] ID" +
                                  ",[MatKhau] MatKhau" +
                                  ",[Email] Email" +
                                  ",[Ten] Ten" +
                                  ",[DiaChi] DiaChi" +
                                  ",[GioiTinh] GioiTinh" +
                                  ",[NgaySinh] NgaySinh" +
                                  ",[NgheNghiep] NgheNghiep" +
                                  ",[DienThoai] DienThoai" +
                                  ",[Diem] Diem" +
                                  ",[idLoaiNguoiDung] LoaiNguoiDung" +
                                  ",[ThoiGianDangNhapTruoc] DangNhapTruoc" +
                                  ",[TenDangNhap] TenDangNhap " +
                            "FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] " + 
                            "WHERE [id] = {0}", uid);
                DataTable data = provider.ExecuteQueryDataTable(sql);
                if (data.Rows.Count > 0)
                {
                    return UserInfo.Load(data);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteUser(int uid)
        {
            try
            {
                sql = string.Format("DELETE FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG ] " +
                                "WHERE [id] = {0}", uid);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int GetUserFreeId()
        {
            try
            {
                int uid = -1;
                sql = string.Format("SELECT MAX([id]) as ID FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG]");
                DataTable table = provider.ExecuteQueryDataTable(sql);
                uid = int.Parse(table.Rows[0]["ID"].ToString()) + 1;
                return (uid);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool InsertUser(int id, string password, UserInfo user)
        {
            try
            {
                sql = string.Format("INSERT INTO [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] " +
                                               "([id]" +
                                               ",[MatKhau]" +
                                               ",[Email]" +
                                               ",[Ten]" +
                                               ",[DiaChi]" +
                                               ",[GioiTinh]" +
                                               ",[NgaySinh]" +
                                               ",[NgheNghiep]" +
                                               ",[DienThoai]" +
                                               ",[Diem]" +
                                               ",[idLoaiNguoiDung]" +
                                               ",[ThoiGianDangNhapTruoc]" +
                                               ",[TenDangNhap]) " +
                                         "VALUES " +
                                               "({0}" +
                                               ",'{1}'" +
                                               ",'{2}'" +
                                               ",N'{3}'" +
                                               ",N'{4}'" +
                                               ",'{5}'" +
                                               ",'{6}'" +
                                               ",N'{7}'" +
                                               ",'{8}'" +
                                               ",{9}" +
                                               ",{10}" +
                                               ",'{11}'" +
                                               ",'{12}')",
                                    id,
                                    password,
                                    user.Email,
                                    user.Name,
                                    user.Address,
                                    user.Gender,
                                    user.BirthDay.ToString(Utilities.DateTimeFormatString),
                                    user.Occupation,
                                    user.Phone,
                                    0,
                                    user.Type,
                                    DateTime.Now.ToString(Utilities.DateTimeFormatString),
                                    user.LoginName);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool UpdateUser(int uid, UserInfo user)
        {
            try
            {
                sql = string.Format("UPDATE [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] " +
                                               "SET [Email] = '{1}'" +
                                                  ",[Ten] = N'{2}'" +
                                                  ",[DiaChi] = N'{3}'" +
                                                  ",[GioiTinh] = '{4}'" +
                                                  ",[NgaySinh] = '{5}'" +
                                                  ",[NgheNghiep] = N'{6}'" +
                                                  ",[DienThoai] = '{7}' " +
                                                  ",[Diem] = {8}" +
                                                  ",[idLoaiNguoiDung] = {9}" +
                                                  ",[ThoiGianDangNhapTruoc] = '{10}'" +
                                             "WHERE [id] = {0}",
                                             uid,
                                             user.Email,
                                             user.Name,
                                             user.Address,
                                             user.Gender,
                                             user.BirthDay.ToString(Utilities.DateTimeFormatString),
                                             user.Occupation,
                                             user.Phone,
                                             user.Score,
                                             user.Type,
                                             user.LastAccess.ToString(Utilities.DateTimeFormatString));

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool ChangePassword(int uid, string pass)
        {
            try
            {
                sql = string.Format("UPDATE [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] " +
                                            "SET [MatKhau] = '{1}' " +
                                            "WHERE [id] = {0}",
                                             uid,
                                             pass);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool CheckPassword(int uid, string pass)
        {
            try
            {
                sql = string.Format("SELECT [MatKhau] Pass " +
                                              "FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] " +
                                              "WHERE [id] = '{0}'",
                                              uid);
                if (pass.Equals(provider.ExecuteQueryDataTable(sql).Rows[0]["Pass"].ToString()))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public string ResetPassword(int uid, string pass)
        {
            try
            {
                sql = string.Format("UPDATE [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] " +
                                       "SET [MatKhau] = '{1}' " +
                                     "WHERE [id] = {0}", uid, pass);
                provider.ExecuteNonQuery(sql, true);
                return pass;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public bool CheckUserInfo(int uid, UserInfo user)
        {
            try
            {
                sql = string.Format("SELECT u.[id] ID" +
                                  ",u.[MatKhau] MatKhau" +
                                  ",u.[Email] Email" +
                                  ",u.[Ten] Ten" +
                                  ",u.[DiaChi] DiaChi" +
                                  ",u.[GioiTinh] GioiTinh" +
                                  ",u.[NgaySinh] NgaySinh" +
                                  ",u.[NgheNghiep] NgheNghiep" +
                                  ",u.[DienThoai] DienThoai" +
                                  ",u.[Diem] Diem" +
                                  ",t.[TenLoaiNguoiDung] LoaiNguoiDung" +
                                  ",u.[ThoiGianDangNhapTruoc] DangNhapTruoc" +
                                  ",u.[TenDangNhap] TenDangNhap " +
                            "FROM [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] u, [COMPUTERSHOPDB].[dbo].[LOAI_NGUOI_DUNG] t " +
                            "WHERE u.[idLoaiNguoiDung] = t.[id] AND u.[id] = {0}", uid);
                DataTable data = provider.ExecuteQueryDataTable(sql);
                if (data.Rows.Count > 0)
                {
                    UserInfo u = UserInfo.Load(data);
                    return (u.Equals(user));
                }
                return false;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public DataTable GetUserType()
        {
            try
            {
                sql = string.Format("SELECT [id] as ID" +
                                          ",[TenLoaiNguoiDung] as TenLoaiNguoiDung " +
                                      "FROM [COMPUTERSHOPDB].[dbo].[LOAI_NGUOI_DUNG]");
                return (provider.ExecuteQueryDataTable(sql));
            }
            catch (Exception)
            {

                throw;
            }
        }
    }
}